﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Odbc;
using System.Data.OracleClient;
using System.Configuration;
using CVSA.Suporte.Sinacor.BMFBovespa.Entidades;

namespace CVSA.Suporte.Sinacor.BMFBovespa.Dados
{

    public class SRM
    {
        private string connString = ConfigurationManager.ConnectionStrings["connStringSqlRemedy"].ToString();
        private string remedyUserName = ConfigurationManager.AppSettings["remedyUserName"].ToString();
        private string remedyPassword = ConfigurationManager.AppSettings["remedyPassword"].ToString();
        private string proxyServer = ConfigurationManager.AppSettings["proxyServer"].ToString();

        public List<Chamado> getWorkOrdersWS(string Classification)
        {

            srmWS.SRM_RequestInterface_WSService obj = new srmWS.SRM_RequestInterface_WSService();
            srmWS.AuthenticationInfo AuthenticationInfoValue = new srmWS.AuthenticationInfo();

            AuthenticationInfoValue.userName = remedyUserName;
            AuthenticationInfoValue.password = Tools.Decrypt(remedyPassword, true);
            obj.AuthenticationInfoValue = AuthenticationInfoValue;
            obj.Timeout = 999999999;

            //string strQuery = @"'Assigned Group' LIKE ""DO-CVSA%""";
            srmWS.GetListOutputMapGetListValues[] dsresult;
            dsresult = obj.Request_QueryList_Service(Classification, "0", string.Empty);

            List<Chamado> chamados = new List<Chamado>();


            foreach (srmWS.GetListOutputMapGetListValues item in dsresult)
            {
                Chamado chamado = new Chamado();
                chamado.Area_Designada = item.Assigned_Group;
                chamado.Data_Abertura = Convert.ToDateTime(item.Submit_Date);
                chamado.Descricao = item.Summary;

                for (int i = 0; i <= item.Details.Length - 1; i++)
                {
                    chamado.Detalhe += item.Details[i].ToString() + Environment.NewLine;
                }

                chamado.DtAtualizacao = DateTime.Now;

                chamado.DtInsercao = DateTime.Now;

                if (!String.IsNullOrEmpty(item.Date_Required))
                {
                    chamado.DtUltimaInteracao = Convert.ToDateTime(item.Date_Required);
                }

                chamado.Empresa = item.Company;
                chamado.IdIncidente = item.Request_ID;
                chamado.Prioridade = item.Impact.ToString();

                chamado.Status = item.Status.ToString();
                chamado.Pessoa_Designada = item.Assignee;
                chamados.Add(chamado);

            }
            return chamados;

        }

        public DataSet getWorkOrdersWS_ds(string Classification)
        {

            srmWS.SRM_RequestInterface_WSService obj = new srmWS.SRM_RequestInterface_WSService();
            srmWS.AuthenticationInfo AuthenticationInfoValue = new srmWS.AuthenticationInfo();

            AuthenticationInfoValue.userName = remedyUserName;
            AuthenticationInfoValue.password = Tools.Decrypt(remedyPassword, true);
            obj.AuthenticationInfoValue = AuthenticationInfoValue;
            obj.Timeout = 999999999;

            srmWS.GetListOutputMapGetListValues[] dsresult;
            dsresult = obj.Request_QueryList_Service(Classification, "0", string.Empty);

            return ConvertToDataSet.ToDataSet<srmWS.GetListOutputMapGetListValues>(dsresult);

        }

        public List<Entidades.SRM> getWorkOrderODBC(string query)
        {
            DataTable odt = new DataTable();
            OdbcConnection cmd = new OdbcConnection("Dsn=AR System ODBC Data Source;arserver=arscorp1;arserverport=50001;UID=" + ConfigurationManager.AppSettings["remedyUserName"] + ";PWD=" + Tools.Decrypt(ConfigurationManager.AppSettings["remedyPassword"], true) + ";aruseunderscores=1;arnamereplace=1;server=NotTheServer");
            OdbcDataAdapter da = new OdbcDataAdapter(query, cmd);
            da.Fill(odt);

            List<Entidades.SRM> chamados = new List<CVSA.Suporte.Sinacor.BMFBovespa.Entidades.SRM>();

            foreach (DataRow row in odt.Rows)
            {
                Entidades.SRM chamado = new CVSA.Suporte.Sinacor.BMFBovespa.Entidades.SRM();
                chamado.Area_Designada = row["ASGRP"].ToString();
                chamado.Contato = row["WO_Type_Field_02"].ToString();
                chamado.Data_Abertura = Convert.ToDateTime(row["Submit_Date"].ToString());
                chamado.Descricao = row["Summary"].ToString();
                chamado.Detalhe = row["WO_Type_Field_01"].ToString();

                if (!String.IsNullOrEmpty(row["Completed_Date"].ToString()))
                {
                    chamado.DtFechamento = Convert.ToDateTime(row["Completed_Date"].ToString());
                }

                chamado.DtUltimaInteracao = Convert.ToDateTime(row["Last_Modified_Date"].ToString());
                chamado.Email = row["WO_Type_Field_03"].ToString();
                chamado.Empresa = row["Customer_Company"].ToString();
                chamado.Modulo = row["WO_Type_Field_05"].ToString();
                chamado.Pessoa_Designada = row["ASCHG"].ToString();
                chamado.Prioridade = row["Priority"].ToString();
                //chamado.Resolucao = row["Resolution"].ToString();
                chamado.SRID = row["SRID"].ToString();
                chamado.Status = row["Status"].ToString();
                chamado.Telefone = row["WO_Type_Field_04"].ToString();
                chamado.WorkOrderID = row["Work_Order_ID"].ToString();
                chamados.Add(chamado);
            }

            return chamados;
        }

        public void SincronizarSRM(List<Entidades.SRM> Chamados)
        {
            OracleConnection conn = new OracleConnection(connString);
            OracleCommand commandExisteChamado = new OracleCommand();
            commandExisteChamado.CommandType = CommandType.Text;

            OracleCommand command = new OracleCommand();
            command.CommandType = CommandType.Text;
            command.Connection = conn;

            commandExisteChamado.Connection = conn;
            commandExisteChamado.Parameters.Add("WorkOrderId", OracleType.Char);

            try
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                foreach (Entidades.SRM chamado in Chamados)
                {
                    // se for novo
                    commandExisteChamado.Parameters["WorkOrderId"].Value = chamado.WorkOrderID;
                    commandExisteChamado.CommandText = "Select 1 from Tb_Chamados_SRM Where Work_Order_ID= :WorkOrderId";
                    int existeChamado = Convert.ToInt32(commandExisteChamado.ExecuteScalar());

                    #region  tratamento dos dados
                    if (existeChamado == 0)
                    {


                        command.Parameters.AddWithValue("WorkOrderID", chamado.WorkOrderID);
                        command.Parameters.AddWithValue("SRID", chamado.SRID);
                        command.Parameters.AddWithValue("Empresa", chamado.Empresa);
                        command.Parameters.AddWithValue("Prioridade", chamado.Prioridade);
                        command.Parameters.AddWithValue("Status", chamado.Status);



                        command.Parameters.AddWithValue("Descricao", chamado.Descricao);

                        if (chamado.Resolucao != null)
                        {
                            command.Parameters.AddWithValue("Resolucao", chamado.Resolucao);
                        }
                        else
                        {
                            command.Parameters.AddWithValue("Resolucao", string.Empty);
                        }


                        if (chamado.Detalhe != null)
                        {
                            command.Parameters.AddWithValue("Detalhe", chamado.Detalhe);
                        }
                        else
                        {
                            command.Parameters.AddWithValue("Detalhe", string.Empty);
                        }


                        command.Parameters.AddWithValue("DataAbertura", chamado.Data_Abertura);
                        command.Parameters.AddWithValue("AreaDesignada", chamado.Area_Designada);
                        command.Parameters.AddWithValue("DataInsercao", DateTime.Today);
                        command.Parameters.AddWithValue("DataUltimaAtualizacao", DateTime.Today);
                        command.Parameters.AddWithValue("DtUltimaInteracao", chamado.DtUltimaInteracao);

                        if (chamado.DtFechamento != DateTime.MinValue)
                        {
                            command.Parameters.AddWithValue("DtFechamento", chamado.DtFechamento);
                        }
                        else
                        {
                            command.Parameters.AddWithValue("DtFechamento", DBNull.Value);
                        }


                        if (!String.IsNullOrEmpty(chamado.Pessoa_Designada))
                        {
                            command.Parameters.AddWithValue("PessoaDesignada", chamado.Pessoa_Designada);
                        }
                        else
                        {
                            command.Parameters.AddWithValue("PessoaDesignada", string.Empty);
                        }

                        if (!String.IsNullOrEmpty(chamado.Contato))
                        {
                            command.Parameters.AddWithValue("Contato", chamado.Contato);
                        }
                        else
                        {
                            command.Parameters.AddWithValue("Contato", string.Empty);
                        }

                        if (!String.IsNullOrEmpty(chamado.Email))
                        {
                            command.Parameters.AddWithValue("Email", chamado.Email);
                        }
                        else
                        {
                            command.Parameters.AddWithValue("Email", string.Empty);
                        }

                        if (!String.IsNullOrEmpty(chamado.Telefone))
                        {
                            command.Parameters.AddWithValue("Telefone", chamado.Telefone);
                        }
                        else
                        {
                            command.Parameters.AddWithValue("Telefone", string.Empty);
                        }


                        if (!String.IsNullOrEmpty(chamado.Modulo))
                        {
                            command.Parameters.AddWithValue("Modulo", chamado.Modulo);
                        }
                        else
                        {
                            command.Parameters.AddWithValue("Modulo", string.Empty);
                        }

                        command.Parameters.AddWithValue("Data_Agendada", chamado.Data_Agendada);


                        command.CommandText = "INSERT INTO Tb_Chamados_SRM (Work_Order_Id,SR_ID,Company,Priority,Status,Description,Resolution,Details,Submit_Date,ASSIGNED_GROUP,DT_RECORDINSERTION,DT_LASTUPDATE,LAST_MODIFIED_DATE,LAST_RESOLVED_DATE,ASSIGNEE,CONTACT,Email,PHONE,CONFIGURATION_ITEM,Scheduled_Date) VALUES (:WorkOrderId,:SRID,:Empresa,:Prioridade,:Status,:Descricao,:Resolucao,:Detalhe,:DataAbertura,:AreaDesignada,:DataInsercao,:DataUltimaAtualizacao,:DtUltimaInteracao,:DtFechamento,:PessoaDesignada,:Contato,:Email,:Telefone,:Modulo,:Data_Agendada )";

                    }
                    else
                    {
                        // se existir



                        command.Parameters.AddWithValue("SRID", chamado.SRID);
                        command.Parameters.AddWithValue("Empresa", chamado.Empresa);
                        command.Parameters.AddWithValue("Prioridade", chamado.Prioridade);
                        command.Parameters.AddWithValue("Status", chamado.Status);



                        command.Parameters.AddWithValue("Descricao", chamado.Descricao);

                        if (chamado.Resolucao != null)
                        {
                            command.Parameters.AddWithValue("Resolucao", chamado.Resolucao);
                        }
                        else
                        {
                            command.Parameters.AddWithValue("Resolucao", string.Empty);
                        }


                        if (chamado.Detalhe != null)
                        {
                            command.Parameters.AddWithValue("Detalhe", chamado.Detalhe);
                        }
                        else
                        {
                            command.Parameters.AddWithValue("Detalhe", string.Empty);
                        }


                        command.Parameters.AddWithValue("AreaDesignada", chamado.Area_Designada);
                        command.Parameters.AddWithValue("DataUltimaAtualizacao", DateTime.Today);
                        command.Parameters.AddWithValue("DtUltimaInteracao", chamado.DtUltimaInteracao);

                        if (chamado.DtFechamento != DateTime.MinValue)
                        {
                            command.Parameters.AddWithValue("DtFechamento", chamado.DtFechamento);
                        }
                        else
                        {
                            command.Parameters.AddWithValue("DtFechamento", DBNull.Value);
                        }


                        if (!String.IsNullOrEmpty(chamado.Pessoa_Designada))
                        {
                            command.Parameters.AddWithValue("PessoaDesignada", chamado.Pessoa_Designada);
                        }
                        else
                        {
                            command.Parameters.AddWithValue("PessoaDesignada", string.Empty);
                        }

                        if (!String.IsNullOrEmpty(chamado.Contato))
                        {
                            command.Parameters.AddWithValue("Contato", chamado.Contato);
                        }
                        else
                        {
                            command.Parameters.AddWithValue("Contato", string.Empty);
                        }

                        if (!String.IsNullOrEmpty(chamado.Email))
                        {
                            command.Parameters.AddWithValue("Email", chamado.Email);
                        }
                        else
                        {
                            command.Parameters.AddWithValue("Email", string.Empty);
                        }

                        if (!String.IsNullOrEmpty(chamado.Telefone))
                        {
                            command.Parameters.AddWithValue("Telefone", chamado.Telefone);
                        }
                        else
                        {
                            command.Parameters.AddWithValue("Telefone", string.Empty);
                        }


                        if (!String.IsNullOrEmpty(chamado.Modulo))
                        {
                            command.Parameters.AddWithValue("Modulo", chamado.Modulo);
                        }
                        else
                        {
                            command.Parameters.AddWithValue("Modulo", string.Empty);
                        }

                        command.Parameters.AddWithValue("Data_Agendada", chamado.Data_Agendada);
                        command.Parameters.AddWithValue("WorkOrderID", chamado.WorkOrderID);

                        command.CommandText = "  UPDATE Tb_Chamados_SRM SET ";
                        command.CommandText += " SR_ID= :SRID, ";
                        command.CommandText += " Company= :Empresa, ";
                        command.CommandText += " Priority= :Prioridade, ";
                        command.CommandText += " Status= :Status, ";
                        command.CommandText += " Description= :Descricao, ";
                        command.CommandText += " Resolution= :Resolucao, ";
                        command.CommandText += " Details= :Detalhe, ";
                        command.CommandText += " ASSIGNED_GROUP= :AreaDesignada, ";
                        command.CommandText += " DT_LASTUPDATE= :DataUltimaAtualizacao, ";
                        command.CommandText += " LAST_MODIFIED_DATE= :DtUltimaInteracao, ";
                        command.CommandText += " LAST_RESOLVED_DATE= :DtFechamento, ";
                        command.CommandText += " ASSIGNEE= :PessoaDesignada, ";
                        command.CommandText += " CONTACT= :Contato, ";
                        command.CommandText += " Email= :Email, ";
                        command.CommandText += " Phone= :Telefone, ";
                        command.CommandText += " CONFIGURATION_ITEM= :Modulo, ";
                        command.CommandText += " Scheduled_Date= :Data_Agendada ";


                        command.CommandText += " WHERE Work_Order_ID = :WorkOrderId";

                    }

                    #endregion


                    try
                    {
                        command.Prepare();
                        command.ExecuteNonQuery();
                        command.Parameters.Clear();
                    }
                    catch (Exception ex)
                    {
                        string e = ex.Message + " - " + ex.InnerException;
                    }
                }
            }

            finally
            {
                if (conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
                command.Dispose();
                conn.Dispose();
            }
        }

        public DateTime UltimoSincronismo()
        {
            OracleConnection conn = new OracleConnection(connString);
            OracleCommand command = new OracleCommand();
            command.CommandType = CommandType.Text;
            command.Connection = conn;
            command.CommandText = "Select Max (LAST_MODIFIED_DATE) from Tb_Chamados_SRM";
            DateTime ultimoRegistro = new DateTime(2009, 1, 1);
            try
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                string _ultimoRegistro = Convert.ToString(command.ExecuteScalar());
                if (!String.IsNullOrEmpty(_ultimoRegistro))
                {
                    ultimoRegistro = Convert.ToDateTime(_ultimoRegistro);
                }

            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
                command.Dispose();
                conn.Dispose();
            }
            return ultimoRegistro;
        }

        public DataTable ObterDadosDB(string clause)
        {
            DataTable odt = new DataTable("Tb_Chamados");
            OracleConnection conn = new OracleConnection(connString);

            OracleCommand command = new OracleCommand();
            command.CommandType = CommandType.Text;
            string sqlcommand = string.Empty;


            sqlcommand = "SELECT * FROM Tb_Chamados_SRM " + clause + " Order By Submit_Date DESC";

            command.CommandText = sqlcommand;
            command.Connection = conn;

            OracleDataAdapter da = new OracleDataAdapter(command);

            try
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }

                da.Fill(odt);

                if (conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }

            }
            catch (OracleException ex)
            {
                throw new Exception(ex.Message, ex.InnerException);
            }
            finally
            {

                da.Dispose();
                command.Dispose();
                conn.Dispose();
            }

            return odt;
        }

        public DataTable ObterChamadosAbertos()
        {
            DataTable odt = new DataTable("Tb_Chamados");
            OracleConnection conn = new OracleConnection(connString);

            OracleCommand command = new OracleCommand();
            command.CommandType = CommandType.Text;
            string sqlcommand = string.Empty;

            sqlcommand = "SELECT * FROM Tb_Chamados_SRM WHERE Status In ('In Progress', 'InProgress','Pending', 'Assigned') Order By Submit_Date DESC";

            command.CommandText = sqlcommand;
            command.Connection = conn;

            OracleDataAdapter da = new OracleDataAdapter(command);

            try
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }

                da.Fill(odt);

                if (conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }

            }
            catch (OracleException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {

                da.Dispose();
                command.Dispose();
                conn.Dispose();
            }

            return odt;
        }
    }
}
